import os
import hashlib
from flask import Flask, request, jsonify, render_template, redirect, url_for, session, Response
from flask_cors import CORS
import pymysql
from dotenv import load_dotenv
import matplotlib
matplotlib.use('Agg')  # 使用非交互式后端
import matplotlib.pyplot as plt
import matplotlib.font_manager as fm
from io import BytesIO
import base64

# 加载环境变量
load_dotenv()

def create_app():
    app = Flask(__name__, template_folder='../templates', static_folder='../static')
    app.secret_key = os.getenv('SECRET_KEY', 'fallback_secret_key')
    CORS(app)  # 允许跨域请求

    # 数据库配置
    db_config = {
        'host': os.getenv('DB_HOST', 'localhost'),
        'port': int(os.getenv('DB_PORT', 3306)),
        'user': os.getenv('DB_USER', 'root'),
        'password': os.getenv('DB_PASSWORD', '123456'),
        'database': os.getenv('DB_NAME', 'zpsjfx'),
        'charset': 'utf8mb4'
    }

    def get_db_connection():
        return pymysql.connect(**db_config)

    def init_database():
        # 创建数据库（如果不存在）
        try:
            # 先连接到MySQL服务器（不指定数据库）
            temp_config = db_config.copy()
            temp_config.pop('database', None)
            temp_conn = pymysql.connect(**temp_config)

            with temp_conn.cursor() as cursor:
                # 创建数据库
                cursor.execute("CREATE DATABASE IF NOT EXISTS `{}` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci".format(db_config['database']))

                # 使用数据库
                cursor.execute("USE `{}`".format(db_config['database']))

                # 创建用户表
                cursor.execute("""
                    CREATE TABLE IF NOT EXISTS users (
                        id INT AUTO_INCREMENT PRIMARY KEY,
                        username VARCHAR(50) NOT NULL UNIQUE,
                        password VARCHAR(255) NOT NULL,
                        email VARCHAR(100),
                        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
                    )
                """)

                # 插入示例用户数据
                cursor.execute("SELECT COUNT(*) FROM users WHERE username = 'admin'")
                result = cursor.fetchone()
                if result and result[0] == 0:
                    cursor.execute("""
                        INSERT INTO users (username, password, email) VALUES
                        ('admin', 'password', 'admin@example.com'),
                        ('user1', 'password', 'user1@example.com')
                    """)

            temp_conn.commit()
            temp_conn.close()

        except Exception as e:
            print(f"数据库初始化失败: {e}")

    # 初始化数据库表
    init_database()

    # 添加模板上下文处理器，使session在所有模板中可用
    @app.context_processor
    def inject_user():
        return dict(session=session)

    @app.route('/api/health', methods=['GET'])
    def health_check():
        return jsonify({'status': 'ok', 'message': 'Backend is running'})

    @app.route('/api/login', methods=['POST'])
    def login():
        try:
            data = request.get_json()
            username = data.get('username')
            password = data.get('password')

            if not username or not password:
                return jsonify({'success': False, 'message': '用户名和密码不能为空'}), 400

            # 连接数据库
            connection = get_db_connection()
            with connection.cursor() as cursor:
                # 查询用户
                sql = "SELECT id, username, password FROM users WHERE username = %s"
                cursor.execute(sql, (username,))
                user = cursor.fetchone()

            connection.close()

            if user:
                # 注意：在实际应用中，这里应该使用bcrypt等库来验证密码
                # 出于演示目的，我们简化处理
                if user[2] == password or user[2] == hashlib_password(password):
                    return jsonify({
                        'success': True,
                        'message': '登录成功',
                        'user': {
                            'id': user[0],
                            'username': user[1]
                        }
                    })
                else:
                    return jsonify({'success': False, 'message': '密码错误'}), 401
            else:
                return jsonify({'success': False, 'message': '用户不存在'}), 404

        except Exception as e:
            return jsonify({'success': False, 'message': f'服务器错误: {str(e)}'}), 500

    def hashlib_password(password):
        """
        简单的密码哈希函数（仅用于演示）
        实际项目中应使用更安全的方案如bcrypt
        """
        import hashlib
        return hashlib.md5(password.encode('utf-8')).hexdigest()

    @app.route('/')
    def index():
        # 如果用户已登录，重定向到仪表板
        if 'user' in session:
            return redirect(url_for('dashboard'))
        # 否则显示登录页面
        return render_template('login.html')

    @app.route('/login', methods=['GET', 'POST'])
    def login_page():
        if request.method == 'POST':
            username = request.form.get('username')
            password = request.form.get('password')

            if not username or not password:
                return render_template('login.html', error='用户名和密码不能为空')

            # 连接数据库
            connection = get_db_connection()
            try:
                with connection.cursor() as cursor:
                    # 查询用户
                    sql = "SELECT id, username, password FROM users WHERE username = %s"
                    cursor.execute(sql, (username,))
                    user = cursor.fetchone()

                if user:
                    # 验证密码
                    if user[2] == password or user[2] == hashlib_password(password):
                        # 登录成功，设置session
                        session['user'] = {
                            'id': user[0],
                            'username': user[1]
                        }
                        return redirect(url_for('home'))
                    else:
                        return render_template('login.html', error='密码错误')
                else:
                    return render_template('login.html', error='用户不存在')
            except Exception as e:
                return render_template('login.html', error=f'服务器错误: {str(e)}')
            finally:
                connection.close()
        else:
            # GET请求，显示登录页面
            return render_template('login.html')

    @app.route('/dashboard')
    def dashboard():
        # 检查用户是否已登录
        if 'user' not in session:
            return redirect(url_for('index'))
        return redirect(url_for('home'))

    @app.route('/logout', methods=['POST'])
    def logout():
        # 清除session
        session.pop('user', None)
        return redirect(url_for('index'))

    # 新增菜单页面路由
    @app.route('/home')
    def home():
        # 检查用户是否已登录
        if 'user' not in session:
            return redirect(url_for('index'))

        try:
            connection = get_db_connection()
            with connection.cursor(pymysql.cursors.DictCursor) as cursor:
                # 获取最新的10条职位数据
                cursor.execute("SELECT title, companyTitle, salary, address FROM boss_jobs ORDER BY id DESC LIMIT 10")
                jobs = cursor.fetchall()

                # 获取用户总数
                cursor.execute("SELECT COUNT(*) FROM users")
                cursor.execute("SELECT COUNT(*) as count FROM users")
                user_count = cursor.fetchone()['count'] or 0

                # 获取职位总数
                cursor.execute("SELECT COUNT(*) as count FROM boss_jobs")
                jobs_count = cursor.fetchone()['count'] or 0

                # 获取公司总数
                cursor.execute("SELECT COUNT(DISTINCT companyTitle) as count FROM boss_jobs")
                companies_count = cursor.fetchone()['count'] or 0

                # 获取最高学历等统计信息...
                cursor.execute("SELECT educational FROM boss_jobs GROUP BY educational ORDER BY COUNT(*) DESC LIMIT 1")
                highest_education_res = cursor.fetchone()
                highest_education = highest_education_res['educational'] if highest_education_res else 'N/A'

                cursor.execute("SELECT salary FROM boss_jobs ORDER BY CAST(SUBSTRING_INDEX(salary, 'K', 1) AS UNSIGNED) DESC LIMIT 1")
                highest_salary_res = cursor.fetchone()
                highest_salary = highest_salary_res['salary'] if highest_salary_res else 'N/A'

                cursor.execute("SELECT address FROM boss_jobs GROUP BY address ORDER BY COUNT(*) DESC LIMIT 1")
                top_region_res = cursor.fetchone()
                top_region = top_region_res['address'] if top_region_res else 'N/A'

                # 获取用户创建时间分布
                cursor.execute("SELECT DATE(created_at) as creation_date, COUNT(*) as count FROM users GROUP BY creation_date ORDER BY creation_date")
                user_creation_data = cursor.fetchall()
                creation_labels = [row['creation_date'].strftime('%Y-%m-%d') for row in user_creation_data if row['creation_date']]
                creation_counts = [row['count'] for row in user_creation_data]

                # 获取最新的5位用户
                cursor.execute("SELECT id, username, email, created_at FROM users ORDER BY id DESC LIMIT 5")
                recent_users = cursor.fetchall()

        except Exception as e:
            print(f"获取统计数据失败: {e}")
            jobs, user_count, jobs_count, companies_count, highest_education, highest_salary, top_region = [], 0, 0, 0, 'N/A', 'N/A', 'N/A'
            creation_labels, creation_counts = [], []
        finally:
            if 'connection' in locals() and connection.open:
                connection.close()

        return render_template('home.html',
                               jobs=jobs,
                               user_count=user_count,
                               jobs_count=jobs_count,
                               companies_count=companies_count,
                               highest_education=highest_education,
                               highest_salary=highest_salary,
                               top_region=top_region,
                               creation_labels=creation_labels,
                               creation_counts=creation_counts,
                               recent_users=recent_users)




    @app.route('/profile')
    def profile():
        # 检查用户是否已登录
        if 'user' not in session:
            return redirect(url_for('index'))
        return render_template('profile.html')


    # API端点：获取当前用户信息
    @app.route('/api/user/profile', methods=['GET'])
    def get_user_profile():
        if 'user' not in session:
            return jsonify({'error': '未登录'}), 401

        user_id = session['user']['id']
        try:
            connection = get_db_connection()
            with connection.cursor() as cursor:
                cursor.execute("SELECT id, username, email FROM users WHERE id = %s", (user_id,))
                user = cursor.fetchone()
            connection.close()

            if user:
                return jsonify({
                    'id': user[0],
                    'username': user[1],
                    'email': user[2]
                })
            else:
                return jsonify({'error': '用户不存在'}), 404
        except Exception as e:
            print(f"获取用户信息时出错: {e}")
            return jsonify({'error': '获取用户信息失败'}), 500

    # API端点：更新用户信息
    @app.route('/api/user/profile', methods=['PUT'])
    def update_user_profile():
        if 'user' not in session:
            return jsonify({'error': '未登录'}), 401

        user_id = session['user']['id']
        data = request.get_json()
        new_username = data.get('username')
        new_email = data.get('email')

        if not new_username or not new_email:
            return jsonify({'error': '用户名和邮箱不能为空'}), 400

        try:
            connection = get_db_connection()
            with connection.cursor() as cursor:
                # 检查新用户名是否已被他人使用
                cursor.execute("SELECT id FROM users WHERE username = %s AND id != %s", (new_username, user_id))
                if cursor.fetchone():
                    return jsonify({'error': '用户名已存在'}), 409

                # 检查新邮箱是否已被他人使用
                cursor.execute("SELECT id FROM users WHERE email = %s AND id != %s", (new_email, user_id))
                if cursor.fetchone():
                    return jsonify({'error': '邮箱已被注册'}), 409

                # 更新用户信息
                cursor.execute("UPDATE users SET username = %s, email = %s WHERE id = %s", (new_username, new_email, user_id))
            connection.commit()
            connection.close()

            # 更新session中的用户名
            session['user']['username'] = new_username
            session.modified = True

            return jsonify({'message': '用户信息更新成功'})
        except Exception as e:
            print(f"更新用户信息时出错: {e}")
            return jsonify({'error': '更新失败，请稍后重试'}), 500

    # API端点：修改密码
    @app.route('/api/user/password', methods=['PUT'])
    def change_password():
        if 'user' not in session:
            return jsonify({'error': '未登录'}), 401

        user_id = session['user']['id']
        data = request.get_json()
        current_password = data.get('current_password')
        new_password = data.get('new_password')
        confirm_password = data.get('confirm_password')

        if not current_password or not new_password or not confirm_password:
            return jsonify({'error': '所有密码字段都不能为空'}), 400

        if new_password != confirm_password:
            return jsonify({'error': '新密码与确认密码不匹配'}), 400

        if len(new_password) < 6:
            return jsonify({'error': '新密码长度至少为6位'}), 400

        try:
            connection = get_db_connection()
            with connection.cursor() as cursor:
                # 验证当前密码
                cursor.execute("SELECT password FROM users WHERE id = %s", (user_id,))
                user = cursor.fetchone()

                if not user:
                    return jsonify({'error': '用户不存在'}), 404

                # 验证当前密码
                if user[0] != current_password and user[0] != hashlib_password(current_password):
                    return jsonify({'error': '当前密码错误'}), 400

                # 更新密码
                hashed_new_password = hashlib_password(new_password)
                cursor.execute("UPDATE users SET password = %s WHERE id = %s", (hashed_new_password, user_id))

            connection.commit()
            connection.close()

            return jsonify({'message': '密码修改成功'})
        except Exception as e:
            print(f"修改密码时出错: {e}")
            return jsonify({'error': '修改密码失败，请稍后重试'}), 500

    @app.route('/data_management')
    def data_management():
        # 检查用户是否已登录
        if 'user' not in session:
            return redirect(url_for('index'))

        try:
            connection = get_db_connection()
            with connection.cursor() as cursor:
                # 获取所有职位数据
                cursor.execute("""
                    SELECT id, jobName, companyName, salary, address, workExperience, educational
                    FROM boss_jobs
                    ORDER BY id DESC
                    LIMIT 50
                """)
                jobs = cursor.fetchall()

            connection.close()
            return render_template('data_management.html', jobs=jobs)

        except Exception as e:
            print(f"获取职位数据时出错: {e}")
            return render_template('data_management.html', jobs=[], error="数据加载失败，请稍后重试。")

    # API端点：获取所有职位数据
    @app.route('/api/jobs', methods=['GET'])
    def get_jobs():
        if 'user' not in session:
            return jsonify({'error': '未登录'}), 401

        page = request.args.get('page', 1, type=int)
        per_page = request.args.get('per_page', 10, type=int)
        search = request.args.get('search', '', type=str)

        try:
            connection = get_db_connection()
            cursor = connection.cursor()

            # 构建查询条件
            where_clause = ""
            params = []
            if search:
                where_clause = "WHERE title LIKE %s OR companyTitle LIKE %s OR address LIKE %s"
                search_param = f"%{search}%"
                params = [search_param, search_param, search_param]

            # 获取总数
            count_query = f"SELECT COUNT(*) FROM boss_jobs {where_clause}"
            cursor.execute(count_query, params)
            total = cursor.fetchone()[0]

            # 获取分页数据
            offset = (page - 1) * per_page
            data_query = f"""
                SELECT id, title, companyTitle, salary, address, workExperience, educational
                FROM boss_jobs {where_clause}
                ORDER BY id DESC
                LIMIT %s OFFSET %s
            """
            cursor.execute(data_query, params + [per_page, offset])
            jobs = cursor.fetchall()

            # 转换为字典格式
            job_list = []
            for job in jobs:
                job_dict = {
                    'id': job[0],
                    'job_name': job[1],       # 前端可能使用 job_name
                    'company_name': job[2],   # 前端可能使用 company_name
                    'salary': job[3],
                    'address': job[4],
                    'experience': job[5],
                    'educational': job[6]
                }
                job_list.append(job_dict)

            connection.close()

            return jsonify({
                'jobs': job_list,
                'total': total,
                'page': page,
                'per_page': per_page,
                'pages': (total + per_page - 1) // per_page
            })

        except Exception as e:
            print(f"获取职位数据时出错: {e}")
            return jsonify({'error': '获取数据失败'}), 500

    # API端点：创建新职位
    @app.route('/api/jobs', methods=['POST'])
    def create_job():
        if 'user' not in session:
            return jsonify({'error': '未登录'}), 401

        data = request.get_json()
        required_fields = ['job_name', 'company_name', 'salary', 'address']

        # 验证必填字段
        for field in required_fields:
            if not data.get(field):
                return jsonify({'error': f'缺少必填字段: {field}'}), 400

        try:
            connection = get_db_connection()
            cursor = connection.cursor()

            insert_query = """
                INSERT INTO boss_jobs (job_name, company_name, salary, address, experience,
                                     educational, job_type, company_type, company_size,
                                     welfare, job_detail, created_at)
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, NOW())
            """

            cursor.execute(insert_query, (
                data.get('job_name'),
                data.get('company_name'),
                data.get('salary'),
                data.get('address'),
                data.get('experience', ''),
                data.get('educational', ''),
                data.get('job_type', ''),
                data.get('company_type', ''),
                data.get('company_size', ''),
                data.get('welfare', ''),
                data.get('job_detail', '')
            ))

            connection.commit()
            job_id = cursor.lastrowid
            connection.close()

            return jsonify({'message': '职位创建成功', 'id': job_id}), 201

        except Exception as e:
            print(f"创建职位时出错: {e}")
            return jsonify({'error': '创建职位失败'}), 500

    # API端点：更新职位
    @app.route('/api/jobs/<int:job_id>', methods=['PUT'])
    def update_job(job_id):
        if 'user' not in session:
            return jsonify({'error': '未登录'}), 401

        data = request.get_json()

        try:
            connection = get_db_connection()
            cursor = connection.cursor()

            # 检查职位是否存在
            cursor.execute("SELECT id FROM boss_jobs WHERE id = %s", (job_id,))
            if not cursor.fetchone():
                connection.close()
                return jsonify({'error': '职位不存在'}), 404

            update_query = """
                UPDATE boss_jobs SET
                    job_name = %s, company_name = %s, salary = %s, address = %s,
                    experience = %s, educational = %s, job_type = %s,
                    company_type = %s, company_size = %s, welfare = %s, job_detail = %s
                WHERE id = %s
            """

            cursor.execute(update_query, (
                data.get('job_name'),
                data.get('company_name'),
                data.get('salary'),
                data.get('address'),
                data.get('experience', ''),
                data.get('educational', ''),
                data.get('job_type', ''),
                data.get('company_type', ''),
                data.get('company_size', ''),
                data.get('welfare', ''),
                data.get('job_detail', ''),
                job_id
            ))

            connection.commit()
            connection.close()

            return jsonify({'message': '职位更新成功'})

        except Exception as e:
            print(f"更新职位时出错: {e}")
            return jsonify({'error': '更新职位失败'}), 500

    # API端点：删除职位
    @app.route('/api/jobs/<int:job_id>', methods=['DELETE'])
    def delete_job(job_id):
        if 'user' not in session:
            return jsonify({'error': '未登录'}), 401

        try:
            connection = get_db_connection()
            cursor = connection.cursor()

            # 检查职位是否存在
            cursor.execute("SELECT id FROM boss_jobs WHERE id = %s", (job_id,))
            if not cursor.fetchone():
                connection.close()
                return jsonify({'error': '职位不存在'}), 404

            cursor.execute("DELETE FROM boss_jobs WHERE id = %s", (job_id,))
            connection.commit()
            connection.close()

            return jsonify({'message': '职位删除成功'})

        except Exception as e:
            print(f"删除职位时出错: {e}")
            return jsonify({'error': '删除职位失败'}), 500



    @app.route('/charts')
    def charts():
        # 检查用户是否已登录
        if 'user' not in session:
            return redirect(url_for('index'))
        return render_template('charts.html')


    # Matplotlib 图表生成
    def set_chinese_font():
        # 尝试找到一个可用的中文字体
        font_names = ['SimHei', 'Microsoft YaHei', 'Arial Unicode MS', 'sans-serif']
        for font_name in font_names:
            try:
                fm.findfont(fm.FontProperties(family=font_name))
                plt.rcParams['font.family'] = font_name
                plt.rcParams['axes.unicode_minus'] = False # 正常显示负号
                print(f"使用字体: {font_name}")
                return
            except Exception:
                continue
        print("未找到可用的中文字体，图表可能显示异常")

    set_chinese_font()

    @app.route('/charts/city_distribution.png')
    def plot_city_distribution():
        try:
            connection = get_db_connection()
            with connection.cursor() as cursor:
                cursor.execute("""
                    SELECT address, COUNT(*) as count
                    FROM boss_jobs
                    WHERE address IS NOT NULL AND address != ''
                    GROUP BY address
                    ORDER BY count DESC
                    LIMIT 10
                """)
                data = cursor.fetchall()
            connection.close()

            labels = [row[0] for row in data]
            values = [row[1] for row in data]

            fig, ax = plt.subplots(figsize=(10, 7))
            ax.pie(values, labels=labels, autopct='%1.1f%%', startangle=90, textprops={'fontsize': 12})
            ax.axis('equal')  # 保证饼图是圆的
            ax.set_title('城市职位分布', fontsize=16, pad=20)

            img = BytesIO()
            fig.savefig(img, format='png', bbox_inches='tight')
            plt.close(fig)
            img.seek(0)
            return Response(img.getvalue(), mimetype='image/png')
        except Exception as e:
            print(f"生成城市分布图失败: {e}")
            return "Error generating chart", 500

    @app.route('/charts/education_distribution.png')
    def plot_education_distribution():
        try:
            connection = get_db_connection()
            with connection.cursor() as cursor:
                cursor.execute("""
                    SELECT educational, COUNT(*) as count
                    FROM boss_jobs
                    WHERE educational IS NOT NULL AND educational != ''
                    GROUP BY educational
                    ORDER BY count DESC
                """)
                data = cursor.fetchall()
            connection.close()

            labels = [row[0] for row in data]
            values = [row[1] for row in data]

            fig, ax = plt.subplots(figsize=(10, 6))
            ax.bar(labels, values, color='skyblue')
            ax.set_title('学历要求分布', fontsize=16, pad=20)
            ax.set_ylabel('职位数量', fontsize=12)
            ax.set_xticklabels(labels, rotation=45, ha='right')
            fig.tight_layout()

            img = BytesIO()
            fig.savefig(img, format='png', bbox_inches='tight')
            plt.close(fig)
            img.seek(0)
            return Response(img.getvalue(), mimetype='image/png')
        except Exception as e:
            print(f"生成学历分布图失败: {e}")
            return "Error generating chart", 500

    @app.route('/charts/experience_distribution.png')
    def plot_experience_distribution():
        try:
            connection = get_db_connection()
            with connection.cursor() as cursor:
                cursor.execute("""
                    SELECT workExperience, COUNT(*) as count
                    FROM boss_jobs
                    WHERE workExperience IS NOT NULL AND workExperience != ''
                    GROUP BY workExperience
                    ORDER BY count DESC
                """)
                data = cursor.fetchall()
            connection.close()

            labels = [row[0] for row in data]
            values = [row[1] for row in data]

            fig, ax = plt.subplots(figsize=(10, 6))
            ax.bar(labels, values, color='lightcoral')
            ax.set_title('工作经验要求分布', fontsize=16, pad=20)
            ax.set_ylabel('职位数量', fontsize=12)
            ax.set_xticklabels(labels, rotation=45, ha='right')
            fig.tight_layout()

            img = BytesIO()
            fig.savefig(img, format='png', bbox_inches='tight')
            plt.close(fig)
            img.seek(0)
            return Response(img.getvalue(), mimetype='image/png')
        except Exception as e:
            print(f"生成工作经验分布图失败: {e}")
            return "Error generating chart", 500

    # API端点：获取BOSS直聘数据（旧版，可考虑移除）
    @app.route('/api/boss_jobs')
    def get_boss_jobs():
        # 检查用户是否已登录
        if 'user' not in session:
            return jsonify({'error': '未登录'}), 401

        try:
            # 获取查询参数
            page = int(request.args.get('page', 1))
            per_page = int(request.args.get('per_page', 20))
            search = request.args.get('search', '')

            # 计算偏移量
            offset = (page - 1) * per_page

            # 连接数据库
            connection = get_db_connection()
            with connection.cursor() as cursor:
                # 构建查询条件
                search_param = f'%{search}%'
                if search:
                    sql = """
                    SELECT * FROM boss_jobs
                    WHERE title LIKE %s OR companyTitle LIKE %s OR address LIKE %s
                    ORDER BY id
                    LIMIT %s OFFSET %s
                    """
                    cursor.execute(sql, (search_param, search_param, search_param, per_page, offset))
                else:
                    sql = "SELECT * FROM boss_jobs ORDER BY id LIMIT %s OFFSET %s"
                    cursor.execute(sql, (per_page, offset))

                jobs = cursor.fetchall()

                # 获取总记录数
                if search:
                    count_sql = "SELECT COUNT(*) FROM boss_jobs WHERE title LIKE %s OR companyTitle LIKE %s OR address LIKE %s"
                    cursor.execute(count_sql, (search_param, search_param, search_param))
                else:
                    count_sql = "SELECT COUNT(*) FROM boss_jobs"
                    cursor.execute(count_sql)

                count_result = cursor.fetchone()
                total = count_result[0] if count_result else 0

                # 转换为字典列表
                jobs_list = []
                for job in jobs:
                    # 获取列名
                    columns = [desc[0] for desc in cursor.description]
                    job_dict = dict(zip(columns, job))
                    jobs_list.append(job_dict)

            connection.close()

            return jsonify({
                'jobs': jobs_list,
                'total': total,
                'page': page,
                'per_page': per_page,
                'pages': (total + per_page - 1) // per_page
            })

        except Exception as e:
            return jsonify({'error': f'获取数据失败: {str(e)}'}), 500

    @app.route('/register')
    def register_page():
        return render_template('register.html')

    @app.route('/register', methods=['POST'])
    def register():
        username = request.form.get('username')
        email = request.form.get('email')
        password = request.form.get('password')
        confirm_password = request.form.get('confirm_password')

        # 验证输入
        if not username or not email or not password or not confirm_password:
            return render_template('register.html', error='所有字段都是必填的')

        if password != confirm_password:
            return render_template('register.html', error='两次输入的密码不一致')

        if len(password) < 6:
            return render_template('register.html', error='密码长度至少为6位')

        # 连接数据库
        connection = get_db_connection()
        try:
            with connection.cursor() as cursor:
                # 检查用户名是否已存在
                cursor.execute("SELECT id FROM users WHERE username = %s", (username,))
                if cursor.fetchone():
                    return render_template('register.html', error='用户名已存在')

                # 检查邮箱是否已存在
                cursor.execute("SELECT id FROM users WHERE email = %s", (email,))
                if cursor.fetchone():
                    return render_template('register.html', error='邮箱已被注册')

                # 插入新用户
                sql = "INSERT INTO users (username, password, email) VALUES (%s, %s, %s)"
                cursor.execute(sql, (username, hashlib_password(password), email))

            connection.commit()

            # 注册成功后自动登录
            with connection.cursor() as cursor:
                cursor.execute("SELECT id, username FROM users WHERE username = %s", (username,))
                user = cursor.fetchone()
                if user:
                    session['user'] = {
                        'id': user[0],
                        'username': user[1]
                    }

            return redirect(url_for('home'))

        except Exception as e:
            return render_template('register.html', error=f'注册失败: {str(e)}')
        finally:
            connection.close()

    return app

if __name__ == '__main__':
    app = create_app()
    app.run(host='0.0.0.0', port=5000, debug=True)